package dier;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class xuanke {
	
	//工号返回教师id
	public int fanhuiTeaid(String TeID)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from teacher where TeID=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,TeID);
		ResultSet rs =ptmt.executeQuery();
		int id=0;
		while(rs.next())
		{
			id=rs.getInt("id");
		}
		return id;
	}
	
	//重置密码
	public void chongzhimm(String id,int i)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql=null;
		int iid=0;
		if(i==0)
		{
			sql="update student "
					+ "set pwd=?"
					+ "where StuID= ? ";
			iid=(int)Integer.valueOf(id);
			PreparedStatement ptmt=conn.prepareStatement(sql);
			ptmt.setString(1, "123456");
			ptmt.setInt(2, iid);
			int count=ptmt.executeUpdate();
			ptmt.close();
			conn.close();
		}
		else
		{
			sql="update teacher "
					+ "set pwd=?"
					+ "where TeID= ? ";
			PreparedStatement ptmt=conn.prepareStatement(sql);
			ptmt.setString(1, "123456");
			int count=ptmt.executeUpdate();
			ptmt.close();
			conn.close();
		}
		
		
	}
	
	public int Teaxiugai(String olTeID,String TeID,String Tname,String Zhicheng,String Tclass)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="update teacher "
				+ "set TeID=?,"
				+ "Tname=?,"
				+ "Zhicheng=?,"
				+ "Tclass=?"
				+ "where TeID= ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, TeID);
		ptmt.setString(2, Tname);
		ptmt.setString(3, Zhicheng);
		ptmt.setString(4, Tclass);
		ptmt.setString(5, olTeID);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else
		{
			return 0;
		}
	}
	
	//返回教师对象
	public TEacher Tealiulan(int id)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from teacher where id=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1, id);
		ResultSet rs =ptmt.executeQuery();
		TEacher tea=new TEacher();
		while(rs.next())
		{
			tea.id=rs.getInt("id");
			tea.TeID=rs.getString("TeID");
			tea.Tname=rs.getString("Tname");
			tea.Zhicheng=rs.getString("Zhicheng");
			tea.Tclass=rs.getString("Tclass");
		}
		rs.close();
		ptmt.close();
		conn.close();
		return tea;
	}
	
	//返回教师id
	public int Teafanhui(String Tname)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from teacher where Tname=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Tname);
		ResultSet rs =ptmt.executeQuery();
		int id=0;
		while(rs.next())
		{
			id=rs.getInt("id");
		}
		rs.close();
		ptmt.close();
		conn.close();
		return id;
	}
	
	//教师修改密码
	public int Teaxiugaimm(String Tname,String opwd,String newpwd)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="select * from teacher where Tname = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,Tname);
		ResultSet rs =ptmt.executeQuery();
		String pwd=null;
		while(rs.next())
		{
			pwd=rs.getString("pwd");
		}
		if(!opwd.equals(pwd))
		{
			rs.close();
			ptmt.close();
			conn.close();
			return 0;//密码不对
		}
		else
		{
			sql="update teacher set pwd = ? where Tname = ? ";
			ptmt=conn.prepareStatement(sql);
			ptmt.setString(1,newpwd);
			ptmt.setString(2,Tname);
			ptmt.executeUpdate();
			rs.close();
			ptmt.close();
			conn.close();
			return 1;
		}
	}
	//返回学生id
	public int Stuchfanhuiid(String Stuname)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from student where Stuname=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Stuname);
		STudent stu=new STudent();
		ResultSet rs =ptmt.executeQuery();
		int StuID=0;
		while(rs.next())
		{
			StuID=rs.getInt("StuID");
			
		}
		rs.close();
		ptmt.close();
		conn.close();
		return StuID;
	}
	
	public STudent Stuchaxunziji(int StuID)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from student where StuID=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1, StuID);
		STudent stu=new STudent();
		ResultSet rs =ptmt.executeQuery();
		
		while(rs.next())
		{
			stu.StuID=rs.getInt("StuID");
			stu.Stuname=rs.getString("Stuname");
			stu.Sex=rs.getString("Sex");
			stu.Age=rs.getString("Age");
			stu.Zhuanye=rs.getString("Zhuanye");
			stu.ClassRoom=rs.getString("ClassRoom");
		}
		rs.close();
		ptmt.close();
		conn.close();
		return stu;
	}
	
	//修改教师所教授课程部分信息
	public void Texiugaikecheng(int ClID,int CPeople)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="update Lesson set CPeople=? where ClID=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1, CPeople);
		ptmt.setInt(2, ClID);
		ptmt.executeUpdate();
		ptmt.close();
		conn.close();
	}
	
	
	public int[] Techaxunkebiao(String CTename) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from lesson where CTename = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, CTename);
		ResultSet rs =ptmt.executeQuery();
		int ret=0;
		int arr[]=new int [20];
		while(rs.next())
		{
			arr[ret]=rs.getInt("ClID");
			ret++;
		}
		rs.close();
		ptmt.close();
		conn.close();
		return arr;
	}
	
	public int[] Stuchaxunkebiao(String Stuname) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from student_lesson where Stuname = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Stuname);
		ResultSet rs =ptmt.executeQuery();
		int ret=0;
		int arr[]=new int [20];
		while(rs.next())
		{
			arr[ret]=rs.getInt("ClID");
			ret++;
		}
		rs.close();
		ptmt.close();
		conn.close();
		return arr;
	}
	
	public void Stuxuanke(String Name,int ClID) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="insert into student_lesson(Stuname,ClID) values(?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Name);
		ptmt.setInt(2, ClID);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		
	}
	
	
	public int[] findallkecheng()throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from Lesson";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		int arr[]=new int[15];
		ResultSet rs =ptmt.executeQuery();
		int ret=0;
		while(rs.next())
		{
			arr[ret]=rs.getInt("ClID");
			ret++;
		}
		rs.close();
		ptmt.close();
		conn.close();
		return arr;
	}
	public Lesson showkecheng(int id) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from Lesson where ClID=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1, id);
		ResultSet rs =ptmt.executeQuery();
		Lesson ls=new Lesson();
		while(rs.next())
		{
			ls.ClID=rs.getInt("ClID");
			ls.Cname=rs.getString("Cname");
			ls.Ctime=rs.getString("Ctime");
			ls.Cmany=rs.getString("Cmany");
			ls.CClass=rs.getString("CClass");
			ls.CTeID=rs.getString("CTeID");
			ls.CTename=rs.getString("CTename");
			ls.CPeople=rs.getString("CPeople");
			ls.CHpeople=rs.getString("CHpeople");
		}
		rs.close();
		ptmt.close();
		conn.close();
		return ls;
	}
	
	public int checkStu(String Cname) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from Lesson where Cname=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,Cname);
		ResultSet rs =ptmt.executeQuery();
		int ret=0;
		while(rs.next())
		{
			ret=rs.getInt("CHpeople");
		}
		rs.close();
		ptmt.close();
		conn.close();
		if(ret==0)
		{
			return 1;
		}
		else
		{
			return 0;
		}
	}
	public int shanchukecheng(String Cname)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="delete from Lesson where Cname = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Cname);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	public int xiugaikecheng(String Cname,String Ctime,String Cmany,String CClass,String CTeID,String CTename,String CPeople) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="update Lesson "
				+ "set Ctime=?,"
				+ "Cmany=?,"
				+ "CClass=?,"
				+ "CTeID=?,"
				+ "CTename=?,"
				+ "CPeople=?"
				+ "where Cname= ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
			ptmt.setString(1, Ctime);
			ptmt.setString(2, Cmany);
			ptmt.setString(3, CClass);
			ptmt.setString(4, CTeID);
			ptmt.setString(5, CTename);
			ptmt.setString(6, CPeople);
			ptmt.setString(7, Cname);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	public int yonghuxiugaimm(String Name,String opwd,String newpwd)throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="select * from Student where Stuname = ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,Name);
		ResultSet rs =ptmt.executeQuery();
		String pwd=null;
		while(rs.next())
		{
			pwd=rs.getString("pwd");
		}
		if(!opwd.equals(pwd))
		{
			rs.close();
			ptmt.close();
			conn.close();
			return 0;//密码不对
		}
		else
		{
			sql="update Student set pwd = ? where Stuname = ? ";
			ptmt=conn.prepareStatement(sql);
			ptmt.setString(1,newpwd);
			ptmt.setString(2,Name);
			ptmt.executeUpdate();
			rs.close();
			ptmt.close();
			conn.close();
			return 1;
		}
	}
	public int denglucheck(String name,String pwd,int a) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql=null;
		if(a==1)
		{
			sql="select * from Student where Stuname = ? ";
		}
		else
		{
			sql="select * from Teacher where Tname = ? ";
		}
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,name);
		ResultSet rs =ptmt.executeQuery();
		String ren=null;
		while(rs.next())
		{
			ren=rs.getString("pwd");
		}
		rs.close();
		ptmt.close();
		conn.close();
		if(pwd.equals(ren))
		{
			return 1;
		}
		return 0;
	}
	
	
	public int checkLe(String CTename) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="select * from Lesson where CTename= ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1,CTename);
		ResultSet rs =ptmt.executeQuery();
		if (!rs.next()) {
			return 0;
		    //rs为空时执行的内容...
		} else {
			return 1;
		    //rs不为空时执行的内容...
		}
	}
	public int shanchuTe(String Tname) throws Exception
	{
	Class.forName("com.mysql.jdbc.Driver");//注册
	String url="jdbc:mysql://localhost:3306/qimo";
	String username="root";
	String Password="qq2665410029";
	Connection conn = DriverManager.getConnection(url, username, Password);//连接
	String sql="delete from Teacher where Tname = ?";
	PreparedStatement ptmt=conn.prepareStatement(sql);
	ptmt.setString(1, Tname);
	int count=ptmt.executeUpdate();
	ptmt.close();
	conn.close();
	if(count>0)
	{
		return 1;
	}
	else {
		return 0;
	}
		
	}
	
	public int addkecheng(String Cname,String Ctime,String Cmany,String CClass,String CTeID,String CTename,String CPeople) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="insert into Lesson(Cname,Ctime,Cmany,CClass,CTeID,CTename,CPeople) values(?,?,?,?,?,?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Cname);
		ptmt.setString(2, Ctime);
		ptmt.setString(3, Cmany);
		ptmt.setString(4, CClass);
		ptmt.setString(5, CTeID);
		ptmt.setString(6, CTename);
		ptmt.setString(7, CPeople);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	public int addTea(String TeID,String Tname,String Zhicheng,String Tclass) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="insert into Teacher(TeID,Tname,Zhicheng,Tclass,pwd) values(?,?,?,?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, TeID);
		ptmt.setString(2, Tname);
		ptmt.setString(3, Zhicheng);
		ptmt.setString(4, Tclass);
		ptmt.setString(5, "123456789");
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	public int checkTe(String ID,String Class) throws Exception
	{
		char [] arr = new char[50];
		arr=ID.toCharArray();
		char c=arr[1];
		String str=null;
		switch(c)
		{
		case '1':str="信息";break;
		case '2':str="土木";break;
		case '3':str="机械";break;
		case '4':str="电气";break;
		case '5':str="交通";break;
		}
		if(str.equals(Class))
		{
			return 1;
		}
		return 0;
	}
	
	public int shanchuStu(String StuID) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="delete from Student where StuID = ?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, StuID);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	
	public int xiugaiStu(String StuID,String Stuname,String Sex,String Age,String Zhuanye,String ClassRoom) throws Exception 
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		String sql="update Student "
				+ "set Stuname=?,"
				+ "Sex=?,"
				+ "Age=?,"
				+ "Zhuanye=?,"
				+ "ClassRoom=?"
				+ "where StuID= ? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
			ptmt.setString(1, Stuname);
			ptmt.setString(2, Sex);
			ptmt.setString(3, Age);
			ptmt.setString(4, Zhuanye);
			ptmt.setString(5, ClassRoom);
			ptmt.setString(6, StuID);
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
	
	public int addStu(String Stuname,String Sex,String Age,String Zhuanye,String ClassRoom) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");//注册
		String url="jdbc:mysql://localhost:3306/qimo";
		String username="root";
		String Password="qq2665410029";
		Connection conn = DriverManager.getConnection(url, username, Password);//连接
		
		String sql="insert into Student(Stuname,Sex,Age,Zhuanye,ClassRoom,pwd) values(?,?,?,?,?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, Stuname);
		ptmt.setString(2, Sex);
		ptmt.setString(3, Age);
		ptmt.setString(4, Zhuanye);
		ptmt.setString(5, ClassRoom);
		ptmt.setString(6, "123456789");
		int count=ptmt.executeUpdate();
		ptmt.close();
		conn.close();
		if(count>0)
		{
			return 1;
		}
		else {
			return 0;
		}
	}
}
